﻿<%@ WebHandler Language="C#" Class="RbWorkcenterShipFinishDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
/**
 * Description: 热表厂各工区转工完成情况看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.5.8
 */

public class RbWorkcenterShipFinishDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getTotalBar":
                        r = getTotalBar(jObject);//分厂转工任务情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getNotCompleteData":
                        r = getNotCompleteData(jObject);//各工区待完成
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getNotCompleteDetails":
                        r = getNotCompleteDetails(jObject);//各工区待完成--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportNotCompleteDetails":
                        exportNotCompleteDetails(jObject);//各工区待完成明细--导出
                        break;
                    case "getWorkcenterBar":
                        r = getWorkcenterBar(jObject);//各工区转工任务情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetails":
                        r = getDetails(jObject);//分厂转工任务情况、各工区转工任务情况--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportDetailsExcel":
                        exportDetailsExcel(jObject);//分厂转工任务情况、各工区转工任务情况--导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //分厂转工任务情况
    Result getTotalBar(JObject jObject)
    {
        string factoryid = jObject["factoryid"].ToString();
        JArray selectdate = JArray.Parse(jObject["needcompletedate"].ToString());
        //查询转工数
        string sql = @"
SELECT NVL(COUNT(DISTINCT cci.containername),0) shipCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus = 1 
      AND pdr.parentid IS NULL
      AND cr.toacceptdate IS NOT NULL
      AND cr.tofactoryid = '{0}'
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')";

        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtResult = dal.GetDataTable(sql);

        dtResult.Columns.Add("completeCount", typeof(string));// 已完成转工数
        dtResult.Columns.Add("intimeCount", typeof(string));// 按时完成转工任务数
        dtResult.Columns.Add("completeRate", typeof(string));// 完成率
        dtResult.Columns.Add("intimeRate", typeof(string));// 按时完成率
        dtResult.Columns.Add("delayDaysSum", typeof(string));// 超期天数

        //查询已完成转工任务数
        sql = @"
SELECT NVL(COUNT(DISTINCT cci.containername),0) completeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' 
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtCompleteCount = dal.GetDataTable(sql);

        //查询按时完成转工任务数
        sql = @"
SELECT NVL(COUNT(DISTINCT cci.containername),0) intimeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' 
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
      AND TRUNC(csr.completedate) <= TRUNC(cr.needcompletedate)
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtInTimeCount = dal.GetDataTable(sql);

        //查询超期天数
        sql = @"
SELECT t.containername,CASE WHEN MAX(t.delayDays) < 0 THEN 0 ELSE MAX(t.delayDays) END delayDays
FROM (
     SELECT cci.containername,cci.togetherflag, 
            CASE WHEN pdr.parentid IS NOT NULL THEN 0
                 WHEN csr.CompleteDate IS NULL THEN TRUNC(SYSDATE) - TRUNC(cr.needcompletedate)
                 ELSE TRUNC(csr.CompleteDate) - TRUNC(cr.needcompletedate)
            END delayDays
     FROM Containershiprecord cr
     LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
     LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
     WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
           AND cr.tofactoryid = '{0}' 
           AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
           AND pdr.parentid IS NULL 
           AND cr.toacceptdate IS NOT NULL
) t 
GROUP BY t.containername";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtDelayDays = dal.GetDataTable(sql);

        dtResult.Rows[0]["completeCount"] = dtCompleteCount.Rows[0]["completeCount"];
        dtResult.Rows[0]["intimeCount"] = dtInTimeCount.Rows[0]["intimeCount"];
        dtResult.Rows[0]["delayDaysSum"] = dtDelayDays.Compute("Sum(delayDays)", "");

        if (dtResult.Rows[0]["shipCount"].ToString() == "0")
        {
            dtResult.Rows[0]["completeRate"] = 0;
            dtResult.Rows[0]["intimeRate"] = 0;
        }
        else
        {
            int completeCount = Convert.ToInt32(dtResult.Rows[0]["completeCount"].ToString());
            int intimeCount = Convert.ToInt32(dtResult.Rows[0]["intimeCount"].ToString());
            double shipCount = Convert.ToDouble(dtResult.Rows[0]["shipCount"].ToString());

            double completeRate = (completeCount / shipCount) * 100;
            double intimeRate = (intimeCount / shipCount) * 100;
            dtResult.Rows[0]["completeRate"] = completeRate.ToString("0.00");
            dtResult.Rows[0]["intimeRate"] = intimeRate.ToString("0.00");
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区待完成统计
    Result getNotCompleteData(JObject jObject)
    {
        string factoryid = jObject["factoryid"].ToString();
        string sql = string.Format("SELECT w.workcentername FROM workcenter w WHERE NVL(w.wctype,0) = 0 AND w.factoryid = '{0}'", factoryid);
        DataTable dtWorkcenter = dal.GetDataTable(sql);
        dtWorkcenter.Columns.Add("notcompleteQty", typeof(Int32));

        //待完成
        sql = @"
SELECT t.workcentername,COUNT(t.workcentername) notcompleteQty 
FROM (
  SELECT cci.containername,w.workcentername
  FROM containershiprecord cr
  LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
  LEFT JOIN containercurrentinfo cci ON cr.containerid = cci.containerid
  LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid 
  LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
  WHERE cr.tofactoryid = '{0}' AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus = 1 
        AND cr.toacceptdate IS NOT NULL
        AND NVL(csr.recivedate,csr.completedate) IS NULL 
        AND pdr.parentid IS NULL
  GROUP BY cci.containername,w.workcentername
) t
GROUP BY t.workcentername";
        sql = string.Format(sql, factoryid);
        DataTable dtTaskInfo = dal.GetDataTable(sql);

        foreach (DataRow dr in dtWorkcenter.Rows)
        {
            string filter = string.Format("workcentername = '{0}'", dr["workcentername"]);
            DataRow[] drTaskInfo = dtTaskInfo.Select(filter);
            if (drTaskInfo.Length > 0)
            {
                dr["notcompleteQty"] = Convert.ToInt32(drTaskInfo[0]["notcompleteQty"].ToString());
            }
            else
            {
                dr["notcompleteQty"] = 0;
            }
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区转工任务情况
    Result getWorkcenterBar(JObject jObject)
    {
        string factoryid = jObject["factoryid"].ToString();
        JArray selectdate = JArray.Parse(jObject["needcompletedate"].ToString());
        string sql = string.Format("SELECT w.workcentername FROM workcenter w WHERE NVL(w.wctype,0) = 0 AND w.factoryid = '{0}'", factoryid);
        DataTable dtWorkcenter = dal.GetDataTable(sql);
        dtWorkcenter.Columns.Add("shipCount", typeof(string));          // 转工数
        dtWorkcenter.Columns.Add("completeCount", typeof(string));      // 已完成转工数
        dtWorkcenter.Columns.Add("intimeCount", typeof(string));        // 按时完成转工任务数
        dtWorkcenter.Columns.Add("completeRate", typeof(string));       // 完成率
        dtWorkcenter.Columns.Add("intimeRate", typeof(string));         // 按时完成率
        dtWorkcenter.Columns.Add("delayDaysSum", typeof(string));       // 超期天数

        //热表各工区--转工数
        sql = @"
SELECT w.workcentername,NVL(COUNT(DISTINCT cci.containername),0) shipCount
FROM containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' 
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL
GROUP BY w.workcentername";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtShipCount = dal.GetDataTable(sql);

        //热表各工区--已完成转工任务数
        sql = @"
SELECT w.workcentername,NVL(COUNT(DISTINCT cci.containername),0) completeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' 
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd') 
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL
GROUP BY w.workcentername";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtCompleteCount = dal.GetDataTable(sql);

        //热表各工区--按时完成转工任务数
        sql = @"
SELECT w.workcentername,NVL(COUNT(DISTINCT cci.containername),0) intimeCount
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE NVL(csr.recivedate,csr.completedate) IS NOT NULL AND cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
      AND cr.tofactoryid = '{0}' 
      AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd') 
      AND TRUNC(csr.completedate) <= TRUNC(cr.needcompletedate)
      AND pdr.parentid IS NULL 
      AND cr.toacceptdate IS NOT NULL
GROUP BY w.workcentername";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtInTimeCount = dal.GetDataTable(sql);

        //热表各工区--超期天数
        sql = @"
SELECT temp.workcentername,temp.containername,CASE WHEN MAX(temp.delayDays) < 0 THEN 0 ELSE MAX(temp.delayDays) END delayDays
FROM (
     SELECT w.workcentername,cci.containername,cci.togetherflag,
            CASE WHEN pdr.parentid IS NOT NULL THEN 0
                 WHEN csr.CompleteDate IS NULL THEN TRUNC(SYSDATE) - TRUNC(cr.needcompletedate)
                 ELSE TRUNC(csr.CompleteDate) - TRUNC(cr.needcompletedate)
            END delayDays
     FROM Containershiprecord cr
     LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
     LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
     LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
     WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 
           AND cr.tofactoryid = '{0}' 
           AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd') 
           AND pdr.parentid IS NULL 
           AND cr.toacceptdate IS NOT NULL
) Temp 
GROUP BY temp.containername,temp.workcentername";
        sql = string.Format(sql, factoryid, selectdate[0].ToString(), selectdate[1].ToString());
        DataTable dtDelayDays = dal.GetDataTable(sql);

        foreach (DataRow dr in dtWorkcenter.Rows)
        {
            string filter = string.Format("workcentername = '{0}'", dr["workcentername"]);

            DataRow[] drShipCount = dtShipCount.Select(filter);
            DataRow[] drCompleteCount = dtCompleteCount.Select(filter);
            DataRow[] drIntimeCount = dtInTimeCount.Select(filter);
            DataRow[] drDelayDays = dtDelayDays.Select(filter);

            if (drShipCount.Length > 0)
            {
                dr["shipCount"] = drShipCount[0]["shipCount"];
            }
            else
            {
                dr["shipCount"] = "0";
            }

            if (drCompleteCount.Length > 0)
            {
                dr["completeCount"] = drCompleteCount[0]["completeCount"];
            }
            else
            {
                dr["completeCount"] = "0";
            }

            if (drIntimeCount.Length > 0)
            {
                dr["intimeCount"] = drIntimeCount[0]["intimeCount"];
            }
            else
            {
                dr["intimeCount"] = "0";
            }

            if (drDelayDays.Length > 0)
            {
                dr["delayDaysSum"] = dtDelayDays.Compute("Sum(delayDays)", filter);
            }
            else
            {
                dr["delayDaysSum"] = "0";
            }

            if (dr["shipCount"].ToString() == "0")
            {
                dr["completeRate"] = 0;
                dr["intimeRate"] = 0;
            }
            else
            {
                int completeCount = Convert.ToInt32(dr["completeCount"].ToString());
                int intimeCount = Convert.ToInt32(dr["intimeCount"].ToString());
                double shipCount = Convert.ToDouble(dr["shipCount"].ToString());

                double completeRate = (completeCount / shipCount) * 100;
                double intimeRate = (intimeCount / shipCount) * 100;
                dr["completeRate"] = completeRate.ToString("0.00");
                dr["intimeRate"] = intimeRate.ToString("0.00");
            }
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //分厂转工任务情况、各工区转工任务情况公用sql
    string getDetailsSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,w.workcentername,cci.containername,cci.qty,cci.productname,cci.productdescription,
       ws.workflowstepname outstepname,ws2.workflowstepname instepname,
       cr.senddate,e2.fullname sender,
       cr.needcompletedate,cr.estimatedcompletedate,
       cr.toacceptdate,e.fullname toaccepter,
       csr.completedate,csr.recivedate,e3.fullname reciver,
       CASE WHEN csr.reciver IS NOT NULL THEN '已转入'
            WHEN csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '待转入'
            WHEN cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '待完成'
       END status,
       CASE WHEN csr.completedate IS NULL THEN TRUNC(SYSDATE) - TRUNC(cr.needcompletedate)
            ELSE TRUNC(csr.completedate) - TRUNC(cr.needcompletedate)
       END delayDays
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
LEFT JOIN workflowstep ws ON ws.workflowstepid = cr.workflowstepid
LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cr.toworkflowstepid
LEFT JOIN employee e ON e.employeeid = cr.toacceptor
LEFT JOIN employee e2 ON e2.employeeid = cr.sender
LEFT JOIN employee e3 ON e3.employeeid = csr.reciver
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus = 1 AND pdr.parentid IS NULL AND cr.toacceptdate IS NOT NULL
     AND cr.tofactoryid = '{0}' 
     AND cr.needcompletedate >= to_date('{1}','yyyy/mm/dd') AND cr.needcompletedate <= to_date('{2}','yyyy/mm/dd')
";
        JArray selectdate = JArray.Parse(jObject["needcompletedate"].ToString());
        sql = string.Format(sql, jObject["factoryid"].ToString(), selectdate[0].ToString(), selectdate[1].ToString());

        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND w.workcentername = '{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["seriesName"] != null)
        {
            string seriesName = jObject["seriesName"].ToString();
            if (seriesName == "超期天数")
            {
                sql += @"
AND ((csr.completedate IS NULL AND TRUNC(SYSDATE) > TRUNC(cr.needcompletedate)) 
     OR (csr.completedate IS NOT NULL AND TRUNC(csr.completedate) > TRUNC(cr.needcompletedate)))";
            }
            else if (seriesName == "完成率")
            {
                sql += @" AND csr.completedate IS NOT NULL";
            }
            else
            {
                sql += @" AND csr.completedate IS NOT NULL AND TRUNC(csr.completedate) <= TRUNC(cr.needcompletedate)";
            }
        }
        return sql;
    }

    //分厂转工任务情况、各工区转工任务情况--明细
    Result getDetails(JObject jObject)
    {
        string sql = getDetailsSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //分厂转工任务情况、各工区转工任务情况--导出
    void exportDetailsExcel(JObject jObject)
    {
        string sql = getDetailsSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["outstepname"].Caption = "转出工序";
        dtResult.Columns["instepname"].Caption = "转入工序";
        dtResult.Columns["senddate"].Caption = "转出时间";
        dtResult.Columns["sender"].Caption = "转出人";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["toaccepter"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["reciver"].Caption = "转入人";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["delaydays"].Caption = "超期天数";

        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }

    //各工区待完成明细公用sql
    string getNotCompleteDetailsSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,w.workcentername,cci.containername,cci.qty,cci.productname,cci.productdescription,
       ws.workflowstepname outstepname,ws2.workflowstepname instepname,
       cr.senddate,e2.fullname sender,
       cr.needcompletedate,cr.estimatedcompletedate,
       cr.toacceptdate,e.fullname toaccepter,
       csr.completedate,csr.recivedate,e3.fullname reciver,
       CASE WHEN csr.reciver IS NOT NULL THEN '已转入'
            WHEN csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '待转入'
            WHEN cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '待完成'
       END status,
       CASE WHEN csr.completedate IS NULL THEN TRUNC(SYSDATE) - TRUNC(cr.needcompletedate)
            ELSE TRUNC(csr.completedate) - TRUNC(cr.needcompletedate)
       END delayDays
FROM Containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
LEFT JOIN workflowstep ws ON ws.workflowstepid = cr.workflowstepid
LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cr.toworkflowstepid
LEFT JOIN employee e ON e.employeeid = cr.toacceptor
LEFT JOIN employee e2 ON e2.employeeid = cr.sender
LEFT JOIN employee e3 ON e3.employeeid = csr.reciver
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus = 1 AND pdr.parentid IS NULL AND cr.toacceptdate IS NOT NULL
     AND csr.completedate IS NULL
     AND cr.tofactoryid = '{0}'
";
        sql = string.Format(sql, jObject["factoryid"].ToString());
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND w.workcentername = '{0}'", jObject["workcentername"].ToString());
        }

        sql += " ORDER BY cci.containername";
        return sql;
    }

    //各工区待完成明细--查询
    Result getNotCompleteDetails(JObject jObject)
    {
        string sql = getNotCompleteDetailsSql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区待完成明细--导出
    void exportNotCompleteDetails(JObject jObject)
    {
        string sql = getNotCompleteDetailsSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["outstepname"].Caption = "转出工序";
        dtResult.Columns["instepname"].Caption = "转入工序";
        dtResult.Columns["senddate"].Caption = "转出时间";
        dtResult.Columns["sender"].Caption = "转出人";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["toaccepter"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["reciver"].Caption = "转入人";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["delaydays"].Caption = "超期天数";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }
}